package com.ody.util.code.model;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.odianyun.db.dao.mapper.ColumnMapper;
import com.odianyun.db.dao.mapper.TableMapper;
import com.odianyun.db.sql.DBConfig;
import com.odianyun.db.sql.SQLExecutor;
import com.odianyun.util.exception.ExceptionUtils;
import com.odianyun.util.io.Closer;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

public class DatabaseModelProvider extends POModelProvider {
	private TableMapper tableMapper;
	private ColumnMapper columnMapper;
	
	private String tablePattern;
	private String columnPattern;
	
	private DataSource dataSource;
	
	private String jdbcDriver;
	private String jdbcUrl;
	private String jdbcUsername;
	private String jdbcPassword;
	
	private String catalog;
	private String schemaPattern;
	private boolean notSupportedCataLog;
	private boolean notSupportedSchema;
	
	public DatabaseModelProvider() {}

	@Override
	protected List<POBean> getPOList() {
		SQLExecutor exec = createSQLExecutor();
		Connection conn = null;
		try {
			conn = exec.getConnection();
			DatabaseMetaData dbmd = conn.getMetaData();
			if (this.catalog == null && !notSupportedCataLog) {
				try {
					this.catalog = conn.getCatalog();
				} catch (Throwable e) {
					this.notSupportedCataLog = true;
				}
			}
			if (this.schemaPattern == null && !notSupportedSchema) {
				try {
					this.schemaPattern = conn.getSchema();
				} catch (Throwable e) {
					this.notSupportedSchema = true;
				}
			}
            TablePatternParser tableParser = new TablePatternParser(tablePattern, "Table");
			PatternParser columnParser = new PatternParser(columnPattern, "Column");
			
			return getModels(dbmd, tableParser, columnParser);
		} catch (Exception e) {
			throw ExceptionUtils.wrap2Runtime(e);
		} finally {
			Closer.close(conn);
		}
	}

	private List<POBean> getModels(DatabaseMetaData dbmd, TablePatternParser tableParser, PatternParser columnParser) throws Exception {
		ResultSet rs = null;
		try {
			List<POBean> rslt = Lists.newArrayList();
			rs = dbmd.getTables(catalog, schemaPattern, null, new String[]{"TABLE"});
			Map<String, String[]> tableCommMap = Maps.newHashMap();
			while (rs.next()) {
				String table = rs.getString("TABLE_NAME");
				String comment = rs.getString("REMARKS");
                tableCommMap.put(table.toLowerCase(), new String[] {table, comment});
			}
			Map<String, POBean> poBeanMap = Maps.newHashMap();
            for (Map.Entry<String, String[]> entry : tableCommMap.entrySet()) {
                String tableLowerCase = entry.getKey();
                String table = entry.getValue()[0];
                String comment = entry.getValue()[0];
    
                String modelName = tableMapper.tableName2EntityName(table);
                if (matches(tableLowerCase, tableParser)) {
                    POBean model = new POBean(modelName, table, comment);
                    model.setFields(getFields(modelName, dbmd, table, columnParser));
                    rslt.add(model);
                    poBeanMap.put(table.toLowerCase(), model);
                }
            }
            for (TablePatternParser.Rel rel : tableParser.getOneToOneList()) {
                POBean model = poBeanMap.get(rel.getTable().toLowerCase());
                Assert.notNull(model, "tablePattern配置错误:" + rel.getTable());
                RelPOBean relModel = getRelModel(model, dbmd, columnParser, tableCommMap, rel);
                model.getRelOneList().add(relModel);
                rslt.add(relModel);
            }
            for (TablePatternParser.Rel rel : tableParser.getOneToManyList()) {
                POBean model = poBeanMap.get(rel.getTable().toLowerCase());
                Assert.notNull(model, "tablePattern配置错误:" + rel.getTable());
                RelPOBean relModel = getRelModel(model, dbmd, columnParser, tableCommMap, rel);
                model.getRelManyList().add(relModel);
                rslt.add(relModel);
            }
			return rslt;
		} finally {
			Closer.close(rs);
		}
	}
    
    private RelPOBean getRelModel(POBean model, DatabaseMetaData dbmd, PatternParser columnParser, Map<String, String[]> tableCommMap, TablePatternParser.Rel rel) throws Exception {
        String[] tm = tableCommMap.get(rel.getRelTable().toLowerCase());
        Assert.notNull(tm, "tablePattern配置错误:" + rel.getRelTable());
        String table = tm[0];
        String comment = tm[1];
        String modelName = tableMapper.tableName2EntityName(rel.getRelTable());
    
        List<FieldBean> fields = getFields(modelName, dbmd, table, columnParser);
        FieldBean mainField = model.getFields().stream().filter(f -> f.getColumn().equalsIgnoreCase(rel.getKey())).findFirst().get();
        FieldBean relField = fields.stream().filter(f -> f.getColumn().equalsIgnoreCase(rel.getRelKey())).findFirst().get();
        
        RelPOBean relModel = new RelPOBean(model, mainField, relField, modelName, table, comment);
        relModel.setFields(fields);
        return relModel;
    }
    
    private List<FieldBean> getFields(String modelName, DatabaseMetaData dbmd, String table, PatternParser columnParser) throws Exception {
		ResultSet rs = null;
		try {
			List<FieldBean> rslt = Lists.newArrayList();
			List<String> pks = getPks(dbmd, table);
            rs = dbmd.getColumns(catalog, schemaPattern, table, null);
			while (rs.next()) {
				String column = rs.getString("COLUMN_NAME");
				String comment = rs.getString("REMARKS");
				int dataType = rs.getInt("DATA_TYPE");
				String nullable = rs.getString("IS_NULLABLE");
				int columnSize = rs.getInt("COLUMN_SIZE");
                int decimalDigits = rs.getInt("DECIMAL_DIGITS");
				String fieldName = columnMapper.column2Field(table, column);
                boolean isCommon = columnParser.isPublic(column.toLowerCase());
				if (matches(column.toLowerCase(), columnParser) || isCommon) {
					String typeFullName = TypeMapping.getTypeFullName(dataType);
					FieldBean field = new FieldBean(fieldName, column, typeFullName, StringUtils.trimAllWhitespace(comment));
					field.setPrimaryKey(pks.contains(column));
					field.setNullable("YES".equals(nullable) ? true : false);
					field.setSize(columnSize);
					field.setPrecision(decimalDigits);
					field.setTable(table);
					field.setModelName(modelName);
					field.setCommon(isCommon);
					rslt.add(field);
				}
			}
			return rslt;
		} finally {
			Closer.close(rs);
		}
	}
	
	private List<String> getPks(DatabaseMetaData dbmd, String table) throws Exception {
		ResultSet rs = null;
		List<String> pks = Lists.newArrayList();
		try {
			rs = dbmd.getPrimaryKeys(catalog, schemaPattern, table);
			while (rs.next()) {
				String column = rs.getString("COLUMN_NAME");
				pks.add(column);
			}
			return pks;
		} finally {
			Closer.close(rs);
		}
	}
	
	private boolean matches(String item, PatternParser parser) {
		return parser.isMatches(item);
	}
	
	private SQLExecutor createSQLExecutor() {
		if (dataSource != null) return new SQLExecutor(dataSource);
		else return new SQLExecutor(createDBConfig());
	}
	
	private DBConfig createDBConfig() {
		DBConfig cfg = new DBConfig();
		cfg.setDriver(jdbcDriver);
		cfg.setUrl(jdbcUrl);
		cfg.setUsername(jdbcUsername);
		cfg.setPassword(jdbcPassword);
		return cfg;
	}
	
	public String getTablePattern() {
		return tablePattern;
	}
	public void setTablePattern(String tablePattern) {
		this.tablePattern = tablePattern;
	}
	public String getColumnPattern() {
		return columnPattern;
	}
	public void setColumnPattern(String columnPattern) {
		this.columnPattern = columnPattern;
	}
	public TableMapper getTableMapper() {
		return tableMapper;
	}
	public void setTableMapper(TableMapper tableMapper) {
		this.tableMapper = tableMapper;
	}
	public ColumnMapper getColumnMapper() {
		return columnMapper;
	}
	public void setColumnMapper(ColumnMapper columnMapper) {
		this.columnMapper = columnMapper;
	}
	public DataSource getDataSource() {
		return dataSource;
	}
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	public String getJdbcDriver() {
		return jdbcDriver;
	}
	public void setJdbcDriver(String jdbcDriver) {
		this.jdbcDriver = jdbcDriver;
	}
	public String getJdbcUrl() {
		return jdbcUrl;
	}
	public void setJdbcUrl(String jdbcUrl) {
		this.jdbcUrl = jdbcUrl;
	}
	public String getJdbcUsername() {
		return jdbcUsername;
	}
	public void setJdbcUsername(String jdbcUsername) {
		this.jdbcUsername = jdbcUsername;
	}
	public String getJdbcPassword() {
		return jdbcPassword;
	}
	public void setJdbcPassword(String jdbcPassword) {
		this.jdbcPassword = jdbcPassword;
	}
	public String getCatalog() {
		return catalog;
	}
	public void setCatalog(String catalog) {
		if (StringUtils.hasText(catalog)) {
			this.catalog = catalog;
		}
	}
	public String getSchemaPattern() {
		return schemaPattern;
	}
	public void setSchemaPattern(String schemaPattern) {
		if (StringUtils.hasText(catalog)) {
			this.schemaPattern = schemaPattern;
		}
	}

}
